if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_gui_lv')
          and type in ('P','PC'))
   drop procedure bd_proc_schedule_gui_lv
go


create procedure bd_proc_schedule_gui_lv (@user_serial_str varchar(max),@begin_date date,@end_date date,@shift_id varchar(50),@ip varchar(max),@operator varchar(max)) as
BEGIN
-- 关闭打印受影响行数
	SET NOCOUNT ON;
-- 定义返回受影响行数结果
	declare @result int = 0;
	--  异常扑捉机制
	BEGIN TRY
			-- 创建用户数据表
			CREATE TABLE #user(
				id int identity,
				user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			)

			-- 规律班次转变的普通班次临时表
			CREATE TABLE #temp(
				id int IDENTITY,
				shift VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
				week  INT, --星期几  1-----7
				days int --当月几号（规律班次是月时使用）
			)
			--排班日期表（每天一条，）
			CREATE TABLE #sechduleDate(
				id int IDENTITY,
				date DATE,
				date_day  INT,
				date_week INT,
				shift VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
				shift_bh  VARCHAR(50)	COLLATE Chinese_PRC_CI_AS,
				shift_is_rest  INT DEFAULT 0
			)

						--创建 返回不满足排班的人的原因
			create table #return_table(
				user_serial VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				user_no VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				dept_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS
			)

			CREATE TABLE #result(
			  user_no varchar(200) COLLATE Chinese_PRC_CI_AS,
			  user_name varchar(200) COLLATE Chinese_PRC_CI_AS,
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  dept_name varchar(200) COLLATE Chinese_PRC_CI_AS,
			  dept_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  shift_id int,
			  shift_bh VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,
			  col_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
			  value VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
			  date DATE,
			  year_month VARCHAR(10) COLLATE Chinese_PRC_CI_AS,
			  shift_is_rest int
			)
			--临时排班表
			CREATE TABLE #kt_paiba (
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  rq nvarchar(10) COLLATE Chinese_PRC_CI_AS,
			  d1 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d2 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d3 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d4 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d5 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d6 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d7 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d8 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d9 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d10 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d11 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d12 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d13 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d14 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d15 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d16 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d17 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d18 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d19 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d20 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d21 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d22 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d23 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d24 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d25 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d26 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d27 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d28 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d29 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d30 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d31 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  gly_no nvarchar(10) COLLATE Chinese_PRC_CI_AS,
			  year int ,
			  month int
			)

		CREATE TABLE #scheduleDetail
      (
        user_serial BIGINT NOT NULL,
        col_name  VARCHAR(30),
        shift_id INT NOT NULL DEFAULT (0),
        shift_name VARCHAR(30) NULL,
        shift_date DATE NOT NULL,
        shift_begin INT NOT NULL DEFAULT (0),
        shift_end INT NOT NULL DEFAULT (0),
        shift_sign_begin INT NOT NULL DEFAULT (0),
        shift_sign_end INT NULL,
        shift_late INT NULL,
        shift_early INT NULL,
        shift_duty_on_delay INT NULL,
        shift_duty_off_delay INT NULL,
        shift_workday INT NULL,
        shift_workday_hours INT NULL,
        is_enjoyment_subsidized INT NULL,
        middle_shift_start_time INT NULL,
        middle_shift_end_time INT NULL,
        night_shift_start_time INT NULL,
        night_shift_end_time INT NULL,
        repast_start_time INT NULL,
        repast_end_time INT NULL,
        repast_start_time2 INT NULL,
        repast_end_time2 INT NULL,
        shift_is_rest INT NULL,
				is_have_ot int null,
				is_created_ot int null
      )


			-- 将规律班次放入普通班次临时表
			insert into #temp(shift)
			SELECT
				bc.value + '|' + ban.mc
			FROM (
				SELECT
				v.value
				,iif(isnull(v.value,'')='','',SUBSTRING(v.value, 4, LEN(v.value) - 3)) bh
				FROM zt_glbc t
				CROSS APPLY STRING_SPLIT(CAST(t.Valuse AS VARCHAR(MAX)), ',') v
				WHERE bh = @shift_id
			) bc
			LEFT JOIN zt_banci ban
			ON ban.bh = bc.bh

			DECLARE @c INT, @ts INT, @lx INT, @shiftCount INT

			SELECT @ts=ts,@lx = lx FROM zt_glbc WHERE bh=@shift_id

			-- lx: 0 日，1 周， 2 月

			-- 规律班次天数
			IF (@lx = 0)
				SET @shiftCount = @ts
			ELSE
			IF (@lx = 1)
				SET @shiftCount = 7 * @ts
			ELSE
			IF (@lx = 2)
				SET @shiftCount = 31

			-- 规律班次记录数,
			SELECT @c=COUNT(1) FROM #temp

			IF(@c < @shiftCount)
			BEGIN
				-- 少于规律班次天数，补齐记录数量,这里就是补末尾没有班次也没有设为休息日的那些日期，中间的有空字符串。

				DECLARE @diff INT, @i INT = 0

				SET @diff = @shiftCount - @c

				WHILE @i < @diff
				BEGIN
					INSERT INTO #temp (shift) VALUES('');
					SET @i+=1
				END
			END


			IF (@lx = 1)
			BEGIN
				--规律（周） 开始是星期1
				--#temp 直到#temp的每条数据都更新
				DECLARE @weekInt int=1 --对应的周几  星期一是1
				SELECT @c=COUNT(1) FROM #temp
				set @i = 1
				while @i<=@c
					BEGIN
						update #temp set week = @weekInt where id = @i;

						set @i = @i+1;
						set @weekInt = @weekInt+1;
						if @weekInt=8
							set @weekInt = 1
					END
			end
			IF (@lx = 2)
			BEGIN
				--规律（月） 开始是1号
				--#temp 直到#temp的每条数据都更新
				SELECT @c=COUNT(1) FROM #temp
				set @i = 1
				while @i<=@c
					BEGIN
						update #temp set days = @i where id = @i;
						set @i = @i+1;
					END
			end

			--循环生成日期
			Declare @oneDate Date = @begin_date
			while @oneDate<=@end_date
				BEGIN
					insert into #sechduleDate(date,date_week,date_day)
					VALUES(
					@oneDate,
					case when DatePart(weekday,@oneDate)-1=0 then 7 else DatePart(weekday,@oneDate)-1 end,
					DatePart(day,@oneDate)
					);
					set @oneDate = dateadd(day,1,@oneDate)
				END

			--lx=0 规律日
			IF (@lx = 0)
				BEGIN
					--规律日：所选日期全部排班，且就一个班次
					update #sechduleDate set shift = (select shift from #temp);
				END
			--lx=0 规律周
			IF (@lx = 1)
				BEGIN
				--规律周：需确定排班的第一天是星期几，则从#temp 第一个出现改星期几的位置向下滚动游标插入数据，每次游标执行完，就检测#sechduleDate插入的位置，
				--如果还没有到最后，则在启动一个游标继续插。
				--DECLARE glbc CURSOR LOCAL  FOR select_statement
				DECLARE @flag int = 1 --循环控制标识
				DECLARE @rowInt int = 1 --更新的行号
				DECLARE @rowTotal int --#sechduleDate总记录数
				select @rowTotal = count(1) from #sechduleDate

				DECLARE @cursorShift varchar(20) --glbc游标中的班次
				DECLARE @cursorWeek int --#glbc游标中的星期数
				DECLARE @fristWeek int --#sechduleDate第一条的星期数
				DECLARE @findFrist int = 1 -- 下方查找第一个匹配的星期数开始更新时使用，找到之后，将该值设置为0.
				select @fristWeek = date_week from #sechduleDate where id = 1;
				while  @flag = 1
					BEGIN
						--当@rowInt 大于等于#sechduleDate的记录数@rowTotal时，将 @flag设为0 停止循环。

						--创建游标
						declare glbc cursor LOCAL for
						select shift,week from #temp

						open glbc

						fetch next from glbc into @cursorShift,@cursorWeek

						while @@FETCH_STATUS=0
						begin
								--第一次游标遍历，跳过星期数不匹配的记录，直到第一个匹配开始
								if @findFrist = 1 and @fristWeek != @cursorWeek
								BEGIN
										print @cursorWeek;
								END
								else BEGIN
									--设置为0，表示已经找到匹配的了。
									set @findFrist = 0;
									update #sechduleDate set shift = @cursorShift where id = @rowInt;
									set @rowInt = @rowInt+1;
								END
								fetch next from glbc into  @cursorShift,@cursorWeek
						end

						close glbc
						deallocate glbc
						--如果所有记录都更新完毕，结束外部循环
						if @rowInt-1 >= @rowTotal
							set @flag = 0
					END
				END
			--lx=0 规律月
			IF (@lx = 2)
				BEGIN
					--规律月：1-31不会超过31，就是最大的一个月的天数，所以只要‘日’对应就行。
					update d
						set shift = t.shift
					from #sechduleDate d
					INNER JOIN #temp t on t.days = DatePart(day,d.date);
				END



			-- 更新班次编号，是否为休息日
			UPDATE #sechduleDate
				SET shift_bh=CASE WHEN ISNULL(shift,'') <> '' THEN SUBSTRING(shift, 4,CHARINDEX('|',shift)-3-1) ELSE '' END
				-- 拆分用户编号保存临时表中
				insert into #user(user_serial)
				select value from string_split(iif(isnull(@user_serial_str,'')='','',@user_serial_str),',');

			-- 封装数据
			insert into #result(user_serial,shift_bh,col_name,value,date,year_month,shift_is_rest)
			select a.user_serial,b.shift_bh,concat('d',b.date_day),b.shift,b.date,convert(varchar(7),b.date,120),b.shift_is_rest
			from #user a,#sechduleDate b


			-- 更新数据的排班ID
			update a set a.shift_id = b.id
			from #result a
			inner join zt_banci b on a.shift_bh = b.bh



			--根据日历，将非工作日的符号改为[X]
			update r
				set r.value = case when (d.is_weekend=1 or d.is_statutory=1) then '[X]'+ SUBSTRING(r.value, 4,len(r.value)) else  r.value end,
						r.shift_is_rest = case when (d.is_weekend=1 or d.is_statutory=1) then 1 else  0 end
			from #result r
			INNER JOIN dt_user u on r.user_serial = u.user_serial
			INNER JOIN bd_kq_work_calendar_detail d on d.parent_id = u.user_work_calendar and r.date = d.work_date




			-- 更新人员信息
			update a set a.user_no = isnull(b.user_no,''), a.user_name = isnull(b.user_lname,''), a.dept_name = isnull(c.dep_name,''), a.dept_serial = isnull(c.dep_serial,'')
			from #result a
			left join dt_user b on a.user_serial = b.user_serial
			left join dt_dep c on b.user_dep = c.dep_serial

			--排班条件校验
			--1 检测是否为封账状态
			insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
			select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,concat(@begin_date,' —— ',@end_date,' 该时段考勤存在已经封账,不能排班')
			from #result a
			left join bd_kq_accounts c on a.user_serial = c.user_serial and year(a.date) = c.year and month(a.date) = c.month
			where isnull(c.lock_status,0)>0
			-- 删除已经封账的数据
			delete a
			from #result a
			inner join #return_table b on a.user_serial = b.user_serial

			--2检测之前是否有排班并且排班有生成的加班单（若该加班单存在，则该人本次全部日期都不能排班）
				insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
        select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,concat(@begin_date,' —— ',@end_date,' 该时段存在排班加班单，不能重新排班')
        from #result a
        inner join bd_kq_schedule_detail d on a.user_serial = d.user_serial and a.date = d.shift_date and d.is_created_ot = 1

        -- 删除已经有加班单的人员数据
        delete a
        from #result a
        inner join #return_table b on a.user_serial = b.user_serial


			 --排班明细数据
			INSERT INTO #scheduleDetail (user_serial, col_name, shift_id, shift_name, shift_date, shift_begin, shift_end, shift_sign_begin, shift_sign_end, shift_late, shift_early, shift_duty_on_delay, shift_duty_off_delay, shift_workday, shift_workday_hours, is_enjoyment_subsidized, middle_shift_start_time, middle_shift_end_time, night_shift_start_time, night_shift_end_time, repast_start_time, repast_end_time, repast_start_time2, repast_end_time2, shift_is_rest)
			SELECT r.user_serial, r.col_name, isnull(bc.id,0), r.value, r.date,isnull(sd.sbsj,0),isnull(sd.xbsj,0),isnull(sd.sbks,0),isnull(sd.xbjs,0),isnull(sd.cdsj,0),isnull(sd.ztsj,0),isnull(sd.bjcd,0),isnull(sd.bjzt,0),isnull(sd.alld,0),isnull(sd.allt,0),isnull(sd.is_enjoyment_subsidized,0),isnull(sd.middle_shift_start_time,0),isnull(sd.middle_shift_end_time,0),isnull(sd.night_shift_start_time,0),isnull(sd.night_shift_end_time,0),isnull(sd.repast_start_time,0),isnull(sd.repast_end_time,0),isnull(sd.repast_start_time2,0),isnull(sd.repast_end_time2,0),r.shift_is_rest
			FROM #result r
			left join zt_banci bc on bc.bh = r.shift_bh
			left join zt_shid sd ON bc.bh = sd.parent and sd.lx=0


			--处理	is_have_ot,is_created_ot
				--班次有加班时段，则is_have_ot = 1 ，is_created_ot=0
				--班次没有加班字段 则is_have_ot = 0 ，is_created_ot=0
				--若当天是休息日，则is_have_ot = 1 ，is_created_ot=0
				update s
					set s.is_have_ot = case when isnull(sd.bh,0)=0 then (case when (isnull(bc.id,0)!=0 and s.shift_is_rest=1) then 1 else 0 end) else 1 end ,
							s.is_created_ot = 0
				from
				#scheduleDetail s
				left join zt_banci bc on bc.id = s.shift_id
				left JOIN zt_shid sd ON bc.bh = sd.parent and sd.lx=1;

				--执行保存
				DECLARE @scheduleDetailJson varchar(max)
				select @scheduleDetailJson = (select * from #scheduleDetail for json path)
        exec bd_proc_action_schedule @scheduleDetailJson,@begin_date,@end_date,@ip,@operator



--返回
				select * from #return_table
		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
	END TRY

	--  异常处理
	BEGIN CATCH
		DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
		IF XACT_STATE() <> 0
		BEGIN
			ROLLBACK TRANSACTION;
		END;
		RAISERROR (@errStr, 16, 1);
	END CATCH

--  清除临时表
	DROP table #user
	DROP TABLE #temp
	DROP TABLE #shift
	DROP TABLE #result
	DROP TABLE #sechduleDate
	DROP TABLE #return_table
  DROP TABLE #kt_paiba

	-- 打开打印受影响行数
	SET NOCOUNT OFF;

END;
go
